package com.xbts.screen.mapper;

import com.xbts.screen.domain.dto.WorkshopCompletionRateDto;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 包装
 */
public interface PackagingMapper {

    @Select("select distinct  CONVERT(VARCHAR(10),dbo.data0048.outtime,23) time,\n" +
            "  \n" +
            "  data0034.yield_object_month monthTarget,DATA0034.yield_object target,\n" +
            "   sum(round(data0048.qty_prod*isnull(data0048.sq_per_unit,data0050.unit_sq),2) ) AS actual\n" +
            "from data0048(nolock) \n" +
            "  inner join data0006(nolock) on data0048.wo_ptr=data0006.rkey\n" +
            "  inner join data0025(nolock)  on  data0006.bom_ptr=data0025.rkey\n" +
            "  left join data0034 b(nolock) on data0048.work_center_ptr=b.rkey\n" +
            "  left join data0034 a(nolock) on b.group_ptr=a.rkey\n" +
            "  left join data0034(nolock)  on b.dept_ptr=data0034.rkey\n" +
            "  left join data0034 t1(nolock) on data0048.to_dept_ptr=t1.rkey\n" +
            "  inner join data0050(nolock)  on data0025.ancestor_ptr=data0050.rkey\n" +
            "  left join data0492(nolock) on data0006.cut_no=data0492.cut_no  \n" +
            "--hsm20210220  left join Data0060(nolock) on Data0492.so_no=Data0060.sales_order\n" +
            "  left join data0010(nolock)  on data0492.customer_ptr=data0010.rkey   left  join data0047(nolock)  on data0047.source_pointer=data0006.bom_ptr and data0047.FLOW_WHSE_PTR=data0006.WHOUSE_PTR   and data0047.parameter_ptr=2 \n" +
            "       left  join data0047 d47b(nolock)                 on     d47b.source_pointer=data0006.bom_ptr and d47b.FLOW_WHSE_PTR=data0006.WHOUSE_PTR   and d47b.parameter_ptr=4\n" +
            "       left  join data0047 d47c(nolock)  on d47c.source_pointer=data0006.bom_ptr  and d47c.FLOW_WHSE_PTR=data0006.WHOUSE_PTR   and d47c.parameter_ptr=7  \n" +
            "  left join data0008(nolock)  on data0008.rkey=data0025.prod_code_ptr\n" +
            "  left  join data0038(nolock)  on data0038.source_ptr=data0025.rkey and  data0038.flow_no=data0048.flow_no and data0038.step_number=data0048.step_no \n" +
            "  left join data0059(nolock) on data0050.rkey=data0059.cust_part_ptr and data0492.customer_ptr=data0059.customer_ptr \n" +
            "\n" +
            "\n" +
            "where \n" +
            "data0048.outtime >= #{startTime} and data0048.outtime <= #{endTime}  AND DATA0034.DEPT_NAME='包装'\n" +
            "and (b.out_source=0 or 2=0) \n" +
            " and  (isnull(data0006.whouse_ptr,0)=0 or exists (select 1 from  get_factory_ptrTable(1) \n" +
            " where whse_rkey=isnull(data0048.make_ptr,data0006.whouse_ptr) )) and data0048.wtype=0  and data0006.wtype=0 \n" +
            "\n" +
            " group by   data0034.yield_object_month ,DATA0034.yield_object ,CONVERT(VARCHAR(10),dbo.data0048.outtime,23)")
    List<WorkshopCompletionRateDto> packagingDetails(@Param("startTime") String startTime, @Param("endTime") String endTime);
}
